﻿using System;
using System.Collections.Generic;
using System.ComponentModel.Composition;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Security;

using Bricks.Component.Utils;
using Bricks.Business.Models;
using Bricks.Business.Models.ExpenseManagement;
using Bricks.Business.Models.ProjectContract;
using Bricks.Site.Models;
using Bricks.Business.Data.Repositories.ProjectContract;
using Bricks.Business.Data.Repositories.ExpenseManagement;
using Bricks.Business.Data.Repositories.EstimateManagement;
using Bricks.Business.Service;
using Bricks.Business.Service.Impl;
using Bricks.Component.Data;
using Bricks.Site.Models.DBViewModel.Contract;
using Bricks.Site.Models.DBViewModel.Expense;


namespace ChiDao.Business.Service.Impl
{
    [Export(typeof(IExpenseService))]
    internal class ExpenseService : IExpenseService
    {
        #region 属性
        [Import]
        protected IApprovalExpenseRepository ApprovalExpenseRepository { get; set; }
        [Import]
        protected ICommonContractInfoRepository CommonContractInfoRepository { get; set; }
        [Import]
        protected IExpenseRepository ExpenseRepository { get; set; }
        [Import]
        protected IExpenseBackupRepository ExpenseBackupRepository { get; set; }
        [Import]
        protected IConstructionContractEepenseBillRepository ConstructionContractEepenseBillRepository { get; set; }
        [Import]
        protected IConstructionContractEepenseBillBackupRepository ConstructionContractEepenseBillBackupRepository { get; set; }
        [Import]
        protected IFlowNodeService FlowNodeService { get; set; }

        [Import]
        protected IUserInfoService UserInfoService { get; set; }
        #endregion

        #region 私有函数
        //存储施工合同已选期次、付款金额等信息
        public class ConstructionContractEepenseBillTemp
        {
            //费用付款ID
            public Guid ExpenseID { get; set; }

            //期次
            public string ContractStages { get; set; }

            //已付款金额
            public decimal? ContractStagesMoney { get; set; }
        }

        //施工合同已选期次、付款金额等信息
        private List<ConstructionContractEepenseBillTemp> ConstructConstructionContractExpenseBillTemp(Guid contractID, Guid expenseID)
        {
            List<ConstructionContractEepenseBillTemp> lstConstructionContractEepenseBillTemp = new List<ConstructionContractEepenseBillTemp>();

            //获取那些审批完和流转中并且不是当前付款单据的付款单据信息
            var lstExpenseInfo = (from e in ExpenseRepository.Entities
                                  where e.ContractID == contractID && e.HandleState != "3" && e.ID != expenseID
                                  select new { ExpenseID = e.ID, e.HandleState, e.FlowCode }).Concat(
                                                            from e in ExpenseBackupRepository.Entities
                                                            where e.ContractID == contractID && e.HandleState == "0" && e.ExpenseID != expenseID
                                                            select new { e.ExpenseID, e.HandleState, e.FlowCode });

            foreach (var expense in lstExpenseInfo)
            {
                //如果是处理完的单据获取单据清单数据，否则获取单据的申请节点的过程数据
                if (expense.HandleState == "2")
                {
                    IQueryable<ConstructionContractEepenseBill> constructionContractEepenseBills = ConstructionContractEepenseBillRepository.Entities.Where(a => a.PID == expense.ExpenseID);
                    foreach (ConstructionContractEepenseBill constructionContractEepenseBill in constructionContractEepenseBills)
                    {
                        lstConstructionContractEepenseBillTemp.Add(new ConstructionContractEepenseBillTemp()
                        {
                            ExpenseID = constructionContractEepenseBill.PID,
                            ContractStages = constructionContractEepenseBill.ContractStages,
                            ContractStagesMoney = constructionContractEepenseBill.ContractStagesMoney
                        });
                    }
                }
                else
                {
                    FlowNodeView nodeView = FlowNodeService.GetFlowNodeByNodeCode(expense.FlowCode, true, "");

                    IQueryable<ConstructionContractEepenseBillBackup> constructionContractEepenseBillBackups = ConstructionContractEepenseBillBackupRepository.Entities.Where(a => a.PID == expense.ExpenseID && a.AssociatedNodeCode == nodeView.NodeCode);
                    foreach (ConstructionContractEepenseBillBackup constructionContractEepenseBillBackup in constructionContractEepenseBillBackups)
                    {
                        lstConstructionContractEepenseBillTemp.Add(new ConstructionContractEepenseBillTemp()
                        {
                            ExpenseID = constructionContractEepenseBillBackup.PID,
                            ContractStages = constructionContractEepenseBillBackup.ContractStages,
                            ContractStagesMoney = constructionContractEepenseBillBackup.ContractStagesMoney
                        });
                    }
                }
            }

            return lstConstructionContractEepenseBillTemp;
        }
        #endregion

        #region 获取单据业务数据、申请没流转单据的过程数据、施工合同下的清单的业务数据
        public ExpenseView GetExpenseByID(string expenseID)
        {
            Guid expenseQueryID = Guid.Parse(expenseID);
            ExpenseView expenseView = null;
            Expense expense = ExpenseRepository.Entities.FirstOrDefault(a => a.ID == expenseQueryID);
            if (expense != null)
            {
                expenseView = new ExpenseView();
                expenseView.ID = expense.ID;
                expenseView.ExpenseID = expense.ID;
                expenseView.FlowCode = expense.FlowCode;
                expenseView.ProjectID = expense.ProjectID;
                expenseView.ContractID = expense.ContractID;
                expenseView.ContractSource = expense.ContractSource;
                expenseView.ExpenseNum = expense.ExpenseNum;
                expenseView.RequestMoneyForRequestPerson = expense.RequestMoneyForRequestPerson;
                expenseView.RequestPerson = expense.RequestPerson;
                var userInfo = UserInfoService.GetUserInfoByName(expense.RequestPerson);
                if (userInfo != null)
                    expenseView.RequestPersonName = userInfo.RealName;
                expenseView.ApprovalMoney = expense.ApprovalMoney;
                expenseView.ContractStages = expense.ContractStages;
                expenseView.RequestDate = expense.RequestDate;
                expenseView.RequeatReason = expense.RequeatReason;
                expenseView.Attachment = expense.Attachment;
                expenseView.PayNum = expense.PayNum;
                expenseView.HandleState = expense.HandleState;
                expenseView.AssociatedNodeCode = "";
                expenseView.LastUpdateUserDepartment = expense.LastUpdateUserDepartment;
                expenseView.LastUpdateUser = expense.LastUpdateUser;
                expenseView.LastUpdateTime = expense.LastUpdateTime;
                expenseView.SaveState = "0";
                expenseView.SendState = "1";
            }
            else
            {
                ExpenseBackup expenseBackup = ExpenseBackupRepository.Entities.FirstOrDefault(a => a.ExpenseID == expenseQueryID);
                if (expenseBackup != null)
                {
                    expenseView = new ExpenseView();
                    expenseView.ID = expenseBackup.ID;
                    expenseView.ExpenseID = expenseBackup.ExpenseID;
                    expenseView.FlowCode = expenseBackup.FlowCode;
                    expenseView.ProjectID = expenseBackup.ProjectID;
                    expenseView.ContractID = expenseBackup.ContractID;
                    expenseView.ContractSource = expenseBackup.ContractSource;
                    expenseView.ExpenseNum = expenseBackup.ExpenseNum;
                    expenseView.RequestMoneyForRequestPerson = expenseBackup.RequestMoneyForRequestPerson;
                    expenseView.RequestPerson = expenseBackup.RequestPerson;
                    expenseView.ApprovalMoney = expenseBackup.ApprovalMoney;
                    expenseView.ContractStages = expenseBackup.ContractStages;
                    expenseView.RequestDate = expenseBackup.RequestDate;
                    expenseView.RequeatReason = expenseBackup.RequeatReason;
                    expenseView.Attachment = expenseBackup.Attachment;
                    expenseView.PayNum = expenseBackup.PayNum;
                    expenseView.HandleState = expenseBackup.HandleState;
                    expenseView.AssociatedNodeCode = expenseBackup.AssociatedNodeCode;
                    expenseView.LastUpdateUserDepartment = expenseBackup.LastUpdateUserDepartment;
                    expenseView.LastUpdateUser = expenseBackup.LastUpdateUser;
                    expenseView.LastUpdateTime = expenseBackup.LastUpdateTime;
                    expenseView.SaveState = expenseBackup.SaveState;
                    expenseView.SendState = expenseBackup.SendState;
                }
            }
            return expenseView;
        }

        public List<ConstructionContractEstimateView> GetConstructionContractEstimateList(Guid contractID, Guid expenseID)
        {
            List<ConstructionContractEstimateView> lstConstructionContractEstimateView = new List<ConstructionContractEstimateView>();
            //var lstMax = from c in ConstructionContractEepenseBillBackupRepository.Entities
            //             where c.ContractID == contractID && c.PID != expenseID
            //             group c by new { newPID = c.PID, newContractStages = c.ContractStages } into newc
            //             select new { newc.Key.newPID, newc.Key.newContractStages, MaxContractStagesMoney = newc.Max(m => m.ContractStagesMoney) };
            List<ConstructionContractEepenseBillTemp> lstConstructionContractEepenseBillTemp = ConstructConstructionContractExpenseBillTemp(contractID, expenseID);
            var lstSumAndCount = from c in lstConstructionContractEepenseBillTemp
                                 group c by c.ContractStages into newc
                                 select new { newc.Key, SumContractStagesMoney = newc.Sum(m => m.ContractStagesMoney), NumContractStages = newc.Count() };
            var lstCCEB = from cceb in ConstructionContractEepenseBillRepository.Entities
                          where cceb.ID == expenseID
                          select cceb;
            string sql = "select * from V_ConstructionContractEstimates where ContractID='" + contractID.ToString() + "' order by ContractStages";
            var lstConstructionContractEstimate = DbSqlHelper<VMConstructionContractEstimates>.SqlQueryCommon(sql);

            var lstReturn = from cce in lstConstructionContractEstimate
                            join sc in lstSumAndCount on cce.ContractStages equals sc.Key into joinedEmpCCE
                            from temp1 in joinedEmpCCE.DefaultIfEmpty()
                            join cceb in lstCCEB on cce.ContractStages equals cceb.ContractStages into joinedEmpCCEB
                            from temp2 in joinedEmpCCEB.DefaultIfEmpty()
                            where cce.ContractID == contractID
                            select new
                                    {
                                        cce.ID,
                                        cce.ContractID,
                                        cce.ContractStages,
                                        cce.OwnersApprovedMoney,
                                        SumContractStagesMoney = temp1 == null ? 0 : temp1.SumContractStagesMoney,
                                        NumContractStages = temp1 == null ? 0 : temp1.NumContractStages,
                                        RequestMoney = temp2 == null ? 0 : temp2.ContractStagesMoney,
                                        IsChecked = temp2 == null ? false : true
                                    };

            foreach (var query in lstReturn)
            {
                lstConstructionContractEstimateView.Add(new ConstructionContractEstimateView()
                {
                    ID = query.ID,
                    ContractID = query.ContractID,
                    ContractStages = query.ContractStages,
                    ContractStagesMoney = query.OwnersApprovedMoney,
                    ApprovalMoney = query.SumContractStagesMoney == null ? 0 : query.SumContractStagesMoney,
                    ApprovalNum = query.NumContractStages,
                    RequestMoney = query.RequestMoney,
                    IsChecked = query.IsChecked
                });
            }
            return lstConstructionContractEstimateView;

        }

        public List<ConstructionContractEepenseBillView> GetBillList(Guid expenseID)
        {
            IQueryable<ConstructionContractEepenseBill> constructionContractEepenseBills = ConstructionContractEepenseBillRepository.Entities.Where(a => a.PID == expenseID);
            List<ConstructionContractEepenseBillView> lstConstructionContractEepenseBillView = new List<ConstructionContractEepenseBillView>();
            foreach (ConstructionContractEepenseBill constructionContractEepenseBill in constructionContractEepenseBills)
            {
                lstConstructionContractEepenseBillView.Add(new ConstructionContractEepenseBillView()
                {
                    ID = Guid.NewGuid(),
                    PID = constructionContractEepenseBill.PID,
                    BillID = constructionContractEepenseBill.ID,
                    ContractID = constructionContractEepenseBill.ContractID,
                    ContractStages = constructionContractEepenseBill.ContractStages,
                    ContractStagesMoney = constructionContractEepenseBill.ContractStagesMoney,
                    AssociatedNodeCode = "",
                    LastUpdateUserDepartment = constructionContractEepenseBill.LastUpdateUserDepartment,
                    LastUpdateUser = constructionContractEepenseBill.LastUpdateUser,
                    LastUpdateTime = constructionContractEepenseBill.LastUpdateTime,
                    SendState = "0"
                });
            }
            return lstConstructionContractEepenseBillView;
        }
        #endregion

        #region  获取单据和施工合同下的清单的过程数据
        public ExpenseView GetExpenseBackupByID(Guid id)
        {
            ExpenseView expenseView = null;
            ExpenseBackup expenseBackup = ExpenseBackupRepository.Entities.FirstOrDefault(a => a.ID == id);
            if (expenseBackup != null)
            {
                expenseView = new ExpenseView();
                expenseView.ID = expenseBackup.ID;
                expenseView.ExpenseID = expenseBackup.ExpenseID;
                expenseView.FlowCode = expenseBackup.FlowCode;
                expenseView.ProjectID = expenseBackup.ProjectID;
                expenseView.ContractID = expenseBackup.ContractID;
                expenseView.ContractSource = expenseBackup.ContractSource;
                expenseView.ExpenseNum = expenseBackup.ExpenseNum;
                expenseView.RequestMoneyForRequestPerson = expenseBackup.RequestMoneyForRequestPerson;
                expenseView.RequestPerson = expenseBackup.RequestPerson;
                expenseView.ApprovalMoney = expenseBackup.ApprovalMoney;
                expenseView.ContractStages = expenseBackup.ContractStages;
                expenseView.RequestDate = expenseBackup.RequestDate;
                expenseView.RequeatReason = expenseBackup.RequeatReason;
                expenseView.Attachment = expenseBackup.Attachment;
                expenseView.PayNum = expenseBackup.PayNum;
                expenseView.HandleState = expenseBackup.HandleState;
                expenseView.AssociatedNodeCode = expenseBackup.AssociatedNodeCode;
                expenseView.LastUpdateUserDepartment = expenseBackup.LastUpdateUserDepartment;
                expenseView.LastUpdateUser = expenseBackup.LastUpdateUser;
                expenseView.LastUpdateTime = expenseBackup.LastUpdateTime;
                expenseView.SaveState = expenseBackup.SaveState;
                expenseView.SendState = expenseBackup.SendState;
            }
            return expenseView;
        }

        public ExpenseView GetExpenseBackupByExpenseID(string expenseID)
        {
            Guid queryID = Guid.Parse(expenseID);
            ExpenseView expenseView = null;
            ExpenseBackup expenseBackup = ExpenseBackupRepository.Entities.Where(a => a.ExpenseID == queryID && a.SendState == "1").OrderByDescending(a => a.LastUpdateTime).FirstOrDefault();
            if (expenseBackup != null)
            {
                expenseView = new ExpenseView();
                expenseView.ID = expenseBackup.ID;
                expenseView.ExpenseID = expenseBackup.ExpenseID;
                expenseView.FlowCode = expenseBackup.FlowCode;
                expenseView.ProjectID = expenseBackup.ProjectID;
                expenseView.ContractID = expenseBackup.ContractID;
                expenseView.ContractSource = expenseBackup.ContractSource;
                expenseView.ExpenseNum = expenseBackup.ExpenseNum;
                expenseView.RequestMoneyForRequestPerson = expenseBackup.RequestMoneyForRequestPerson;
                expenseView.RequestPerson = expenseBackup.RequestPerson;
                var userInfo = UserInfoService.GetUserInfoByName(expenseBackup.RequestPerson);
                if (userInfo != null)
                    expenseView.RequestPersonName = userInfo.RealName;
                expenseView.ApprovalMoney = expenseBackup.ApprovalMoney;
                expenseView.ContractStages = expenseBackup.ContractStages;
                expenseView.RequestDate = expenseBackup.RequestDate;
                expenseView.RequeatReason = expenseBackup.RequeatReason;
                expenseView.Attachment = expenseBackup.Attachment;
                expenseView.PayNum = expenseBackup.PayNum;
                expenseView.HandleState = expenseBackup.HandleState;
                expenseView.AssociatedNodeCode = expenseBackup.AssociatedNodeCode;
                expenseView.LastUpdateUserDepartment = expenseBackup.LastUpdateUserDepartment;
                expenseView.LastUpdateUser = expenseBackup.LastUpdateUser;
                expenseView.LastUpdateTime = expenseBackup.LastUpdateTime;
                expenseView.SaveState = expenseBackup.SaveState;
                expenseView.SendState = expenseBackup.SendState;
            }
            return expenseView;
        }

        public ExpenseView GetExpenseBackupByCondition(string expenseID, string nodeCode, string department, string userName)
        {
            Guid queryID = Guid.Parse(expenseID);
            ExpenseView expenseView = null;
            ExpenseBackup expenseBackup = ExpenseBackupRepository.Entities.FirstOrDefault(a => a.ExpenseID == queryID && a.AssociatedNodeCode == nodeCode && a.LastUpdateUserDepartment == department && a.LastUpdateUser == userName);
            if (expenseBackup != null)
            {
                expenseView = new ExpenseView();
                expenseView.ID = expenseBackup.ID;
                expenseView.ExpenseID = expenseBackup.ExpenseID;
                expenseView.FlowCode = expenseBackup.FlowCode;
                expenseView.ProjectID = expenseBackup.ProjectID;
                expenseView.ContractID = expenseBackup.ContractID;
                expenseView.ContractSource = expenseBackup.ContractSource;
                expenseView.ExpenseNum = expenseBackup.ExpenseNum;
                expenseView.RequestMoneyForRequestPerson = expenseBackup.RequestMoneyForRequestPerson;
                expenseView.RequestPerson = expenseBackup.RequestPerson;
                var userInfo = UserInfoService.GetUserInfoByName(expenseBackup.RequestPerson);
                if (userInfo != null)
                    expenseView.RequestPersonName = userInfo.RealName;
                expenseView.ApprovalMoney = expenseBackup.ApprovalMoney;
                expenseView.ContractStages = expenseBackup.ContractStages;
                expenseView.RequestDate = expenseBackup.RequestDate;
                expenseView.RequeatReason = expenseBackup.RequeatReason;
                expenseView.Attachment = expenseBackup.Attachment;
                expenseView.PayNum = expenseBackup.PayNum;
                expenseView.HandleState = expenseBackup.HandleState;
                expenseView.AssociatedNodeCode = expenseBackup.AssociatedNodeCode;
                expenseView.LastUpdateUserDepartment = expenseBackup.LastUpdateUserDepartment;
                expenseView.LastUpdateUser = expenseBackup.LastUpdateUser;
                expenseView.LastUpdateTime = expenseBackup.LastUpdateTime;
                expenseView.SaveState = expenseBackup.SaveState;
                expenseView.SendState = expenseBackup.SendState;
            }
            return expenseView;
        }

        public List<ConstructionContractEstimateView> GetConstructionContractEstimateBackupList(Guid contractID, Guid expenseID, string nodeCode, string department, string userName)
        {
            List<ConstructionContractEstimateView> lstConstructionContractEstimateView = new List<ConstructionContractEstimateView>();
            //var lstMax = from c in ConstructionContractEepenseBillBackupRepository.Entities
            //             where c.ContractID == contractID && c.PID != expenseID
            //             group c by new { newPID = c.PID, newContractStages = c.ContractStages } into newc
            //             select new { newc.Key.newPID, newc.Key.newContractStages, MaxContractStagesMoney = newc.Max(m => m.ContractStagesMoney) };
            //var lstSumAndCount = from c in lstMax
            //                     group c by c.newContractStages into newc
            //                     select new { newc.Key, SumContractStagesMoney = newc.Sum(m => m.MaxContractStagesMoney), NumContractStages = newc.Count() };
            List<ConstructionContractEepenseBillTemp> lstConstructionContractEepenseBillTemp = ConstructConstructionContractExpenseBillTemp(contractID, expenseID);
            var lstSumAndCount = from c in lstConstructionContractEepenseBillTemp
                                 group c by c.ContractStages into newc
                                 select new { newc.Key, SumContractStagesMoney = newc.Sum(m => m.ContractStagesMoney), NumContractStages = newc.Count() };
            var lstCCEB = from cceb in ConstructionContractEepenseBillBackupRepository.Entities
                          where cceb.PID == expenseID && cceb.AssociatedNodeCode == nodeCode && cceb.LastUpdateUserDepartment == department && cceb.LastUpdateUser == userName
                          select cceb;
            string sql = "select * from V_ConstructionContractEstimates where ContractID='" + contractID.ToString() + "' order by ContractStages";
            var lstConstructionContractEstimate = DbSqlHelper<VMConstructionContractEstimates>.SqlQueryCommon(sql);

            var lstReturn = from cce in lstConstructionContractEstimate
                            join sc in lstSumAndCount on cce.ContractStages equals sc.Key into joinedEmpCCE
                            from temp1 in joinedEmpCCE.DefaultIfEmpty()
                            join cceb in lstCCEB on cce.ContractStages equals cceb.ContractStages into joinedEmpCCEB
                            from temp2 in joinedEmpCCEB.DefaultIfEmpty()
                            where cce.ContractID == contractID
                            select new
                            {
                                cce.ID,
                                cce.ContractID,
                                cce.ContractStages,
                                cce.OwnersApprovedMoney,
                                SumContractStagesMoney = temp1 == null ? 0 : temp1.SumContractStagesMoney,
                                NumContractStages = temp1 == null ? 0 : temp1.NumContractStages,
                                RequestMoney = temp2 == null ? 0 : temp2.ContractStagesMoney,
                                IsChecked = temp2 == null ? false : true
                            };

            foreach (var query in lstReturn)
            {
                lstConstructionContractEstimateView.Add(new ConstructionContractEstimateView()
                {
                    ID = query.ID,
                    ContractID = query.ContractID,
                    ContractStages = query.ContractStages,
                    ContractStagesMoney = query.OwnersApprovedMoney,
                    ApprovalMoney = query.SumContractStagesMoney == null ? 0 : query.SumContractStagesMoney,
                    ApprovalNum = query.NumContractStages,
                    RequestMoney = query.RequestMoney,
                    IsChecked = query.IsChecked
                });
            }
            return lstConstructionContractEstimateView;
        }

        public List<ConstructionContractEepenseBillView> GetBillBackupList(Guid expenseID, string nodeCode, string department, string userName)
        {
            IQueryable<ConstructionContractEepenseBillBackup> constructionContractEepenseBillBackups = ConstructionContractEepenseBillBackupRepository.Entities.Where(a => a.PID == expenseID && a.AssociatedNodeCode == nodeCode && a.LastUpdateUserDepartment == department && a.LastUpdateUser == userName);
            List<ConstructionContractEepenseBillView> lstConstructionContractEepenseBillView = new List<ConstructionContractEepenseBillView>();
            foreach (ConstructionContractEepenseBillBackup constructionContractEepenseBillBackup in constructionContractEepenseBillBackups)
            {
                lstConstructionContractEepenseBillView.Add(new ConstructionContractEepenseBillView()
                {
                    ID = constructionContractEepenseBillBackup.ID,
                    PID = constructionContractEepenseBillBackup.PID,
                    BillID = constructionContractEepenseBillBackup.BillID,
                    ContractID = constructionContractEepenseBillBackup.ContractID,
                    ContractStages = constructionContractEepenseBillBackup.ContractStages,
                    ContractStagesMoney = constructionContractEepenseBillBackup.ContractStagesMoney,
                    AssociatedNodeCode = constructionContractEepenseBillBackup.AssociatedNodeCode,
                    LastUpdateUserDepartment = constructionContractEepenseBillBackup.LastUpdateUserDepartment,
                    LastUpdateUser = constructionContractEepenseBillBackup.LastUpdateUser,
                    LastUpdateTime = constructionContractEepenseBillBackup.LastUpdateTime,
                    SendState = constructionContractEepenseBillBackup.SendState
                });
            }
            return lstConstructionContractEepenseBillView;
        }
        #endregion

        #region 判断其他类合同的本次申请是否超过
        public bool IsPassMoneyForCommonContractExpense(Guid contractID, Guid expenseID, decimal? approvalMoney)
        {
            bool isPass = false;
            //获取那些审批完和流转中并且不是当前付款单据的付款单据信息
            var lstExpenseInfo = (from e in ExpenseRepository.Entities
                                  where e.ContractID == contractID && e.HandleState != "3" && e.ID != expenseID
                                  select new { ExpenseID = e.ID, e.HandleState, e.RequestMoneyForRequestPerson, e.ApprovalMoney }).Concat(
                                                            from e in ExpenseBackupRepository.Entities
                                                            where e.ContractID == contractID && e.HandleState == "0" && e.ExpenseID != expenseID
                                                            select new { e.ExpenseID, e.HandleState, e.RequestMoneyForRequestPerson, e.ApprovalMoney });
            //统计金额
            decimal? totalMoney = 0;
            foreach (var expense in lstExpenseInfo)
            {
                if (expense.HandleState == "2")
                {
                    totalMoney += expense.ApprovalMoney;
                }
                else
                {
                    totalMoney += expense.RequestMoneyForRequestPerson;
                }
            }

            //获取合同信息
            CommonContractInfo commonContractInfo = CommonContractInfoRepository.Entities.SingleOrDefault(c => c.ID == contractID);

            if ((commonContractInfo.ContractMoney - totalMoney - approvalMoney) < 0)
            {
                isPass = true;
            }
            return isPass;
        }
        #endregion

        #region 保存单据和清单的业务数据
        public void SaveExpense(ExpenseView expenseView, List<ConstructionContractEepenseBillView> saveList)
        {

            Expense record = ExpenseRepository.Entities.FirstOrDefault(e => e.ID == expenseView.ExpenseID);
            if (record != null)
            {
                UpdateExpense(record, expenseView);
            }
            else
            {
                InsertExpense(expenseView);
            }

            if (saveList.Count() > 0)
            {
                DeleteConstructionContractEepenseBill(expenseView);
                foreach (ConstructionContractEepenseBillView constructionContractEepenseBillView in saveList)
                {
                    InsertConstructionContractEepenseBill(constructionContractEepenseBillView);
                }
            }
        }

        private void InsertExpense(ExpenseView expenseView)
        {
            Expense record = new Expense()
            {
                ID = expenseView.ExpenseID,
                FlowCode = expenseView.FlowCode,
                ProjectID = expenseView.ProjectID,
                ContractID = expenseView.ContractID,
                ContractSource = expenseView.ContractSource,
                ExpenseNum = expenseView.ExpenseNum,
                RequestMoneyForRequestPerson = expenseView.RequestMoneyForRequestPerson,
                RequestPerson = expenseView.RequestPerson,
                ApprovalMoney = expenseView.ApprovalMoney,
                ContractStages = expenseView.ContractStages,
                RequestDate = expenseView.RequestDate,
                RequeatReason = expenseView.RequeatReason,
                Attachment = expenseView.Attachment,
                PayNum = expenseView.PayNum,
                HandleState = expenseView.HandleState,
                LastUpdateUserDepartment = expenseView.LastUpdateUserDepartment,
                LastUpdateUser = expenseView.LastUpdateUser,
                LastUpdateTime = expenseView.LastUpdateTime
            };
            ExpenseRepository.Insert(record);
        }

        private void UpdateExpense(Expense record, ExpenseView expenseView)
        {
            record.ApprovalMoney = expenseView.ApprovalMoney;
            record.ContractStages = expenseView.ContractStages;
            record.RequeatReason = expenseView.RequeatReason;
            record.Attachment = expenseView.Attachment;
            record.PayNum = expenseView.PayNum;
            record.HandleState = expenseView.HandleState;
            if (string.IsNullOrEmpty(expenseView.LastUpdateUserDepartment))
            {
                record.LastUpdateUserDepartment = expenseView.LastUpdateUserDepartment;
            }
            if (string.IsNullOrEmpty(expenseView.LastUpdateUser))
            {
                record.LastUpdateUser = expenseView.LastUpdateUser;
            }
            record.LastUpdateTime = expenseView.LastUpdateTime;
            ExpenseRepository.Update(record);
        }

        private void DeleteConstructionContractEepenseBill(ExpenseView expenseView)
        {
            IQueryable<ConstructionContractEepenseBill> lstConstructionContractEepenseBill = ConstructionContractEepenseBillRepository.Entities.Where(c => c.PID == expenseView.ExpenseID);
            List<Guid> lstDeleteID = new List<Guid>();
            foreach (ConstructionContractEepenseBill constructionContractEepenseBill in lstConstructionContractEepenseBill)
            {
                lstDeleteID.Add(constructionContractEepenseBill.ID);
            }
            foreach (Guid id in lstDeleteID)
            {
                ConstructionContractEepenseBillRepository.Delete(id);
            }
        }

        private void InsertConstructionContractEepenseBill(ConstructionContractEepenseBillView constructionContractEepenseBillView)
        {
            ConstructionContractEepenseBill record = new ConstructionContractEepenseBill()
            {
                ID = constructionContractEepenseBillView.BillID,
                ContractID = constructionContractEepenseBillView.ContractID,
                PID = constructionContractEepenseBillView.PID,
                ContractStages = constructionContractEepenseBillView.ContractStages,
                ContractStagesMoney = constructionContractEepenseBillView.ContractStagesMoney,
                LastUpdateUserDepartment = constructionContractEepenseBillView.LastUpdateUserDepartment,
                LastUpdateUser = constructionContractEepenseBillView.LastUpdateUser,
                LastUpdateTime = constructionContractEepenseBillView.LastUpdateTime
            };
            ConstructionContractEepenseBillRepository.Insert(record);
        }
        #endregion

        #region 保存单据和清单的过程数据
        public int GetExpenseNum(Guid projectID, Guid contractID)
        {
            int expenseNum = 1;
            var lstExpenseBackup = ExpenseBackupRepository.Entities.Where(eb => eb.ProjectID == projectID && eb.ContractID == contractID);
            if (lstExpenseBackup.Count() > 0)
            {
                var lstMaxExpenseNum = lstExpenseBackup.Max(eb => eb.ExpenseNum);
                if (lstMaxExpenseNum > 0)
                {
                    expenseNum = lstMaxExpenseNum + 1;
                }
            }
            return expenseNum;
        }

        public void SaveExpenseBackup(ExpenseView expenseView, List<ConstructionContractEepenseBillView> saveList)
        {
            ExpenseBackup record = ExpenseBackupRepository.Entities.FirstOrDefault(e => e.ExpenseID == expenseView.ExpenseID && e.AssociatedNodeCode == expenseView.AssociatedNodeCode && e.LastUpdateUserDepartment == expenseView.LastUpdateUserDepartment && e.LastUpdateUser == expenseView.LastUpdateUser);
            if (record != null)
            {
                UpdateExpenseBackup(record, expenseView);
            }
            else
            {
                InsertExpenseBackup(expenseView);
            }

            if (saveList.Count() > 0)
            {
                DeleteConstructionContractEepenseBillBackup(expenseView);
                foreach (ConstructionContractEepenseBillView constructionContractEepenseBillView in saveList)
                {
                    InsertConstructionContractEepenseBillBackup(constructionContractEepenseBillView);
                }
            }
        }

        private void InsertExpenseBackup(ExpenseView expenseView)
        {
            ExpenseBackup record = new ExpenseBackup()
            {
                ID = expenseView.ID,
                FlowCode = expenseView.FlowCode,
                ExpenseID = expenseView.ExpenseID,
                ProjectID = expenseView.ProjectID,
                ContractID = expenseView.ContractID,
                ContractSource = expenseView.ContractSource,
                ExpenseNum = expenseView.ExpenseNum,
                RequestMoneyForRequestPerson = expenseView.RequestMoneyForRequestPerson,
                RequestPerson = expenseView.RequestPerson,
                ApprovalMoney = expenseView.ApprovalMoney,
                ContractStages = expenseView.ContractStages,
                RequestDate = expenseView.RequestDate,
                RequeatReason = expenseView.RequeatReason,
                Attachment = expenseView.Attachment,
                PayNum = expenseView.PayNum,
                HandleState = expenseView.HandleState,
                AssociatedNodeCode = expenseView.AssociatedNodeCode,
                LastUpdateUserDepartment = expenseView.LastUpdateUserDepartment,
                LastUpdateUser = expenseView.LastUpdateUser,
                LastUpdateTime = expenseView.LastUpdateTime,
                SaveState = expenseView.SaveState,
                SendState = expenseView.SendState
            };
            ExpenseBackupRepository.Insert(record);
        }

        private void UpdateExpenseBackup(ExpenseBackup record, ExpenseView expenseView)
        {
            record.RequestMoneyForRequestPerson = expenseView.RequestMoneyForRequestPerson;
            record.ApprovalMoney = expenseView.ApprovalMoney;
            record.ContractStages = expenseView.ContractStages;
            record.RequeatReason = expenseView.RequeatReason;
            record.Attachment = expenseView.Attachment;
            record.PayNum = expenseView.PayNum;
            record.HandleState = expenseView.HandleState;
            record.LastUpdateTime = expenseView.LastUpdateTime;
            record.SaveState = expenseView.SaveState;
            record.SendState = expenseView.SendState;
            ExpenseBackupRepository.Update(record);
        }

        private void DeleteConstructionContractEepenseBillBackup(ExpenseView expenseView)
        {
            IQueryable<ConstructionContractEepenseBillBackup> lstConstructionContractEepenseBillBackup = ConstructionContractEepenseBillBackupRepository.Entities.Where(c => c.PID == expenseView.ExpenseID && c.AssociatedNodeCode == expenseView.AssociatedNodeCode && c.LastUpdateUserDepartment == expenseView.LastUpdateUserDepartment && c.LastUpdateUser == expenseView.LastUpdateUser);
            List<Guid> lstDeleteID = new List<Guid>();
            foreach (ConstructionContractEepenseBillBackup constructionContractEepenseBillBackup in lstConstructionContractEepenseBillBackup)
            {
                lstDeleteID.Add(constructionContractEepenseBillBackup.ID);
            }
            foreach (Guid id in lstDeleteID)
            {
                ConstructionContractEepenseBillBackupRepository.Delete(id);
            }
        }

        private void InsertConstructionContractEepenseBillBackup(ConstructionContractEepenseBillView constructionContractEepenseBillView)
        {
            ConstructionContractEepenseBillBackup record = new ConstructionContractEepenseBillBackup()
            {
                ID = GuidHelper.NewGuid(),
                ContractID = constructionContractEepenseBillView.ContractID,
                PID = constructionContractEepenseBillView.PID,
                BillID = constructionContractEepenseBillView.BillID,
                ContractStages = constructionContractEepenseBillView.ContractStages,
                ContractStagesMoney = constructionContractEepenseBillView.ContractStagesMoney,
                AssociatedNodeCode = constructionContractEepenseBillView.AssociatedNodeCode,
                LastUpdateUserDepartment = constructionContractEepenseBillView.LastUpdateUserDepartment,
                LastUpdateUser = constructionContractEepenseBillView.LastUpdateUser,
                LastUpdateTime = constructionContractEepenseBillView.LastUpdateTime,
                SendState = constructionContractEepenseBillView.SendState
            };
            ConstructionContractEepenseBillBackupRepository.Insert(record);
        }
        #endregion

        #region 流程审批完成，将数据写进已付款费用表
        public void InsertApprovalExpense(ApprovalExpenseView approvalExpenseView)
        {
            ApprovalExpense approvalExpense = new ApprovalExpense();
            approvalExpense.ID = approvalExpenseView.ID;
            approvalExpense.ProjectID = approvalExpenseView.ProjectID;
            approvalExpense.ContractID = approvalExpenseView.ContractID;
            approvalExpense.ExpenseID = approvalExpenseView.ExpenseID;
            approvalExpense.ApprovalMoney = approvalExpenseView.ApprovalMoney;
            ApprovalExpenseRepository.Insert(approvalExpense);
        }
        #endregion

        #region 单据完成或者终止之后，删除所有sendState="0"的数据
        public void DeleteExpenseProcessData(string expenseID)
        {
            string sql = @" DELETE FROM ExpenseBackups WHERE ExpenseID='" + expenseID + "' AND ISNULL(SendState,'0')='0';" +
                           "DELETE FROM ConstructionContractEepenseBillBackups WHERE PID='" + expenseID + "' AND ISNULL(SendState,'0')='0';";
            ExpenseRepository.ExeSqlCommon(sql);
        }
        #endregion

        #region 撤回或者回退的时候处理单据的业务数据
        public void WithdrawOrBackExpenseForRequestNode(string expenseID, string nodeCode)
        {
            string sql = @"DELETE FROM Expenses WHERE ID='" + expenseID + "';" +
                          "DELETE FROM ConstructionContractEepenseBills WHERE PID='" + expenseID + "';" +
                          "DELETE FROM ExpenseBackups WHERE ExpenseID='" + expenseID + "' AND AssociatedNodeCode<>'" + nodeCode + "';" +
                          "DELETE FROM ConstructionContractEepenseBillBackups WHERE PID='" + expenseID + "' AND AssociatedNodeCode<>'" + nodeCode + "';" +
                          "UPDATE ExpenseBackups SET HandleState='0',SaveState='1',SendState='0' WHERE ExpenseID='" + expenseID + "';" +
                          "UPDATE ConstructionContractEepenseBillBackups SET SendState='0' WHERE PID='" + expenseID + "';";
            ExpenseRepository.ExeSqlCommon(sql);
        }

        public void WithdrawOrBackExpenseForOtherNode(string expenseID, string nodeCode, string nodeCodeStr)
        {
            string sql = @"DELETE FROM ConstructionContractEepenseBills WHERE PID='" + expenseID + "';" +
                          "DELETE FROM ExpenseBackups WHERE ExpenseID='" + expenseID + "' AND AssociatedNodeCode IN (" + nodeCodeStr + ");" +
                          "DELETE FROM ConstructionContractEepenseBillBackups WHERE PID='" + expenseID + "' AND AssociatedNodeCode IN (" + nodeCodeStr + ");" +
                          "UPDATE ExpenseBackups SET SendState='0' WHERE ExpenseID='" + expenseID + "' AND AssociatedNodeCode='" + nodeCode + "';" +
                          "UPDATE ConstructionContractEepenseBillBackups SET SendState='0' WHERE PID='" + expenseID + "'AND AssociatedNodeCode='" + nodeCode + "';";
            ExpenseRepository.ExeSqlCommon(sql);
        }
        #endregion

        #region 项目下的标段付款费用列表的界面数据展示
        public List<ContractExpenseView> GetContractExpenseList(string projectID, string correspondenceUnitStr, List<Guid> lstCorrespondenceUnit)
        {
            List<ContractExpenseView> lstContractExpenseView = new List<ContractExpenseView>();
            if (String.IsNullOrEmpty(projectID))
                return lstContractExpenseView;
            //设置变量：查询ID,施工类父ID,其他类父ID
            Guid queryID = Guid.Parse(projectID);
            Guid constructionID = Guid.NewGuid();
            Guid commonID = Guid.NewGuid();

            //构造分类节点：施工类和其他类
            ConstructTypeData(lstContractExpenseView, constructionID, commonID, queryID);

            //从施工合同视图获取指定项目下的合同信息
            string sql = "select * from V_ContractInfoes where pid='" + projectID + "'  ORDER BY LevelCode";
            if (!string.IsNullOrEmpty(correspondenceUnitStr))
            {
                sql = "select * from V_ContractInfoes where pid='" + projectID + "' and CorrespondenceUnit in (" + correspondenceUnitStr.Trim(',') + ")  ORDER BY LevelCode";
            }
            var lstContractInfoView = DbSqlHelper<VMContractInfo>.SqlQueryCommon(sql);

            var lstCommonContractInfoView = CommonContractInfoRepository.Entities;
            if (lstCorrespondenceUnit.Count > 0)
            {
                lstCommonContractInfoView = from c in CommonContractInfoRepository.Entities
                                            where lstCorrespondenceUnit.Contains(c.CorrespondenceUnit)
                                            select c;
            }

            //合并合同信息
            var lstAllContract = (from c in lstContractInfoView
                                  select new ContractInfoView
                                  {
                                      PID = c.PID,
                                      ID = c.ID,
                                      ContractName = c.ContractName,
                                      ContractMoney = c.ContractMoney,
                                      ConstructionDrawMoney = c.ConstructionDrawMoney,
                                      AlterIncreaseOrDecreaseMoney = c.AlterIncreaseOrDecreaseMoney,
                                      AfterAlterMoney = c.AfterAlterMoney,
                                      DisplayPID = constructionID,
                                      ContractSource = "Construction"
                                  }).Concat(
                                        from c in lstCommonContractInfoView
                                        where c.PID == queryID
                                        select new ContractInfoView
                                        {
                                            PID = c.PID,
                                            ID = c.ID,
                                            ContractName = c.ContractName,
                                            ContractMoney = c.ContractMoney,
                                            ConstructionDrawMoney = c.ContractMoney,
                                            AlterIncreaseOrDecreaseMoney = 0,
                                            AfterAlterMoney = c.ContractMoney,
                                            DisplayPID = commonID,
                                            ContractSource = "Common"
                                        });

            //从已审批费用付款表里面汇总数据
            var lstExpense = from a in ApprovalExpenseRepository.Entities
                             group a by a.ContractID into newa
                             select new { newa.Key, TotalApprovalMoney = newa.Sum(m => m.ApprovalMoney) };

            //获取施工合同和其他类合同的拨付款数据
            var lstReturn = from c in lstAllContract
                            join a in lstExpense on c.ID equals a.Key into joinedEmpExpense
                            from empA in joinedEmpExpense.DefaultIfEmpty()
                            select new
                            {
                                c.PID,
                                c.ID,
                                c.ContractName,
                                ContractMoney = c.ContractMoney == null ? 0 : c.ContractMoney,
                                ConstructionDrawMoney = c.ConstructionDrawMoney == null ? 0 : c.ConstructionDrawMoney,
                                AlterIncreaseOrDecreaseMoney = c.AlterIncreaseOrDecreaseMoney == null ? 0 : c.AlterIncreaseOrDecreaseMoney,
                                AfterAlterMoney = c.AfterAlterMoney == null ? 0 : c.AfterAlterMoney,
                                TotalApprovalMoney = empA == null ? 0 : empA.TotalApprovalMoney,
                                c.DisplayPID,
                                c.ContractSource
                            };

            foreach (var query in lstReturn)
            {
                lstContractExpenseView.Add(new ContractExpenseView()
                {
                    ID = Guid.NewGuid(),
                    ProjectID = query.PID,
                    ContractID = query.ID,
                    ContractName = query.ContractName,
                    ContractMoney = query.ContractMoney,
                    ConstructionDrawMoney = query.ConstructionDrawMoney,
                    AlterIncreaseOrDecreaseMoney = query.AlterIncreaseOrDecreaseMoney,
                    AfterAlterMoney = query.AfterAlterMoney,
                    ApprovalMoney = query.TotalApprovalMoney,
                    PaymentPercentage = query.ContractMoney == 0 || query.TotalApprovalMoney == 0 ? "0%" : Math.Round(Convert.ToDecimal(query.TotalApprovalMoney / query.AfterAlterMoney * 100), 2).ToString() + "%",
                    DisplayPID = query.DisplayPID,
                    ContractSource = query.ContractSource
                });
            }
            return lstContractExpenseView;
        }

        //构造分类节点：施工类和其他类
        private void ConstructTypeData(List<ContractExpenseView> lstContractExpenseView, Guid constructionID, Guid commonID, Guid projectID)
        {
            lstContractExpenseView.Add(new ContractExpenseView()
            {
                ID = constructionID,
                ProjectID = projectID,
                ContractID = Guid.Empty,
                ContractName = "施工类",
                DisplayPID = Guid.NewGuid(),
                ContractSource = ""
            });
            lstContractExpenseView.Add(new ContractExpenseView()
            {
                ID = commonID,
                ProjectID = projectID,
                ContractID = Guid.Empty,
                ContractName = "其他类",
                DisplayPID = Guid.NewGuid(),
                ContractSource = ""
            });
        }
        #endregion

        #region 合同下的付款列表的界面数据展示和删除选中项
        public List<ExpenseView> GetExpenseList(Guid contractID)
        {
            var queryList = from e in ExpenseRepository.Entities
                            where e.ContractID == contractID
                            select new
                            {
                                e.ID,
                                e.FlowCode,
                                e.ProjectID,
                                e.ContractID,
                                e.ContractSource,
                                e.ExpenseNum,
                                e.RequestMoneyForRequestPerson,
                                e.RequestPerson,
                                e.ApprovalMoney,
                                e.ContractStages,
                                e.RequestDate,
                                e.RequeatReason,
                                e.HandleState,
                                e.LastUpdateUserDepartment,
                                e.LastUpdateUser,
                                e.LastUpdateTime
                            };

            List<ExpenseView> expenseViewList = new List<ExpenseView>();
            ExpenseView newExpenseView;
            foreach (var query in queryList)
            {
                //expenseViewList.Add(new ExpenseView()
                //{
                //    ID = GuidHelper.NewGuid(),
                //    ExpenseID = query.ID,
                //    FlowCode = query.FlowCode,
                //    ProjectID = query.ProjectID,
                //    ContractID = query.ContractID,
                //    ContractSource = query.ContractSource,
                //    ExpenseNum = query.ExpenseNum,
                //    RequestMoneyForRequestPerson = query.RequestMoneyForRequestPerson,
                //    RequestPerson = query.RequestPerson,

                //    ApprovalMoney = query.ApprovalMoney,
                //    ContractStages = query.ContractStages,
                //    RequestDate = query.RequestDate,
                //    RequeatReason = query.RequeatReason,
                //    HandleState = query.HandleState,
                //    AssociatedNodeCode = "",
                //    LastUpdateUserDepartment = query.LastUpdateUserDepartment,
                //    LastUpdateUser = query.LastUpdateUser,
                //    LastUpdateTime = query.LastUpdateTime
                //});
                newExpenseView = new ExpenseView();
                newExpenseView.ID = GuidHelper.NewGuid();
                newExpenseView.ExpenseID = query.ID;
                newExpenseView.FlowCode = query.FlowCode;
                newExpenseView.ProjectID = query.ProjectID;
                newExpenseView.ContractID = query.ContractID;
                newExpenseView.ContractSource = query.ContractSource;
                newExpenseView.ExpenseNum = query.ExpenseNum;
                newExpenseView.RequestMoneyForRequestPerson = query.RequestMoneyForRequestPerson;
                newExpenseView.RequestPerson = query.RequestPerson;
                //根据用户名取得用户真实姓名
                var userInfo = UserInfoService.GetUserInfoByName(query.RequestPerson);
                if (userInfo != null)
                    newExpenseView.RequestPersonName = userInfo.RealName;
                newExpenseView.ApprovalMoney = query.ApprovalMoney;
                newExpenseView.ContractStages = query.ContractStages;
                newExpenseView.RequestDate = query.RequestDate;
                newExpenseView.RequeatReason = query.RequeatReason;
                newExpenseView.HandleState = query.HandleState;
                newExpenseView.AssociatedNodeCode = "";
                newExpenseView.LastUpdateUserDepartment = query.LastUpdateUserDepartment;
                newExpenseView.LastUpdateUser = query.LastUpdateUser;
                newExpenseView.LastUpdateTime = query.LastUpdateTime;
                expenseViewList.Add(newExpenseView);
            }

            var queryProcessList = from e in ExpenseBackupRepository.Entities
                                   where e.ContractID == contractID && e.HandleState == "0"
                                   select new
                                   {
                                       e.ExpenseID,
                                       e.FlowCode,
                                       e.ProjectID,
                                       e.ContractID,
                                       e.ContractSource,
                                       e.ExpenseNum,
                                       e.RequestMoneyForRequestPerson,
                                       e.RequestPerson,
                                       e.ApprovalMoney,
                                       e.ContractStages,
                                       e.RequestDate,
                                       e.RequeatReason,
                                       e.HandleState,
                                       e.AssociatedNodeCode,
                                       e.LastUpdateUserDepartment,
                                       e.LastUpdateUser,
                                       e.LastUpdateTime
                                   };

            foreach (var query in queryProcessList)
            {
                //expenseViewList.Add(new ExpenseView()
                //{
                //    ID = GuidHelper.NewGuid(),
                //    ExpenseID = query.ExpenseID,
                //    FlowCode = query.FlowCode,
                //    ProjectID = query.ProjectID,
                //    ContractID = query.ContractID,
                //    ContractSource = query.ContractSource,
                //    ExpenseNum = query.ExpenseNum,
                //    RequestMoneyForRequestPerson = query.RequestMoneyForRequestPerson,
                //    RequestPerson = query.RequestPerson,
                //    ApprovalMoney = query.ApprovalMoney,
                //    ContractStages = query.ContractStages,
                //    RequestDate = query.RequestDate,
                //    RequeatReason = query.RequeatReason,
                //    HandleState = query.HandleState,
                //    AssociatedNodeCode = query.AssociatedNodeCode,
                //    LastUpdateUserDepartment = query.LastUpdateUserDepartment,
                //    LastUpdateUser = query.LastUpdateUser,
                //    LastUpdateTime = query.LastUpdateTime
                //});

                newExpenseView = new ExpenseView();
                newExpenseView.ID = GuidHelper.NewGuid();
                newExpenseView.ExpenseID = query.ExpenseID;
                newExpenseView.FlowCode = query.FlowCode;
                newExpenseView.ProjectID = query.ProjectID;
                newExpenseView.ContractID = query.ContractID;
                newExpenseView.ContractSource = query.ContractSource;
                newExpenseView.ExpenseNum = query.ExpenseNum;
                newExpenseView.RequestMoneyForRequestPerson = query.RequestMoneyForRequestPerson;
                newExpenseView.RequestPerson = query.RequestPerson;
                //根据用户名取得用户真实姓名
                var userInfo = UserInfoService.GetUserInfoByName(query.RequestPerson);
                if (userInfo != null)
                    newExpenseView.RequestPersonName = userInfo.RealName;
                newExpenseView.ApprovalMoney = query.ApprovalMoney;
                newExpenseView.ContractStages = query.ContractStages;
                newExpenseView.RequestDate = query.RequestDate;
                newExpenseView.RequeatReason = query.RequeatReason;
                newExpenseView.HandleState = query.HandleState;
                newExpenseView.AssociatedNodeCode = "";
                newExpenseView.LastUpdateUserDepartment = query.LastUpdateUserDepartment;
                newExpenseView.LastUpdateUser = query.LastUpdateUser;
                newExpenseView.LastUpdateTime = query.LastUpdateTime;
                expenseViewList.Add(newExpenseView);
            }

            return expenseViewList.OrderBy(e => e.ContractStages).ToList();
        }

        public OperationResult DeleteSelectExpense(string expenseID)
        {
            try
            {
                PublicHelper.CheckArgument(expenseID, "id");
                Guid guidID = Guid.Parse(expenseID);
                Expense record = ExpenseRepository.Entities.FirstOrDefault(m => m.ID == guidID);
                ExpenseBackup recordBackup = ExpenseBackupRepository.Entities.FirstOrDefault(m => m.ExpenseID == guidID);
                if (record == null && recordBackup == null)
                    return new OperationResult(OperationResultType.QueryNull, "未找到指定的记录");
                if (record != null)
                {
                    ExpenseRepository.Delete(record);
                }

                string sql = @"DELETE FROM ConstructionContractEepenseBills WHERE PID='" + expenseID + "';" +
                              "DELETE FROM ExpenseBackups WHERE ExpenseID='" + expenseID + "';" +
                              "DELETE FROM ConstructionContractEepenseBillBackups WHERE PID='" + expenseID + "';" +
                              "DELETE FROM ApprovalExpenses WHERE ExpenseID='" + expenseID + "';" +
                              "DELETE FROM BaklogTasks WHERE RecordID='" + expenseID + "';" +
                              "DELETE FROM BaklogTaskTemps WHERE RecordID='" + expenseID + "';" +
                              "DELETE FROM CompleteFlowNodes WHERE RecordID='" + expenseID + "';" +
                              "DELETE FROM FlowGraphNodes WHERE RecordID='" + expenseID + "';" +
                              "DELETE FROM FlowProcessMessages WHERE RecordID='" + expenseID + "';" +
                              "DELETE FROM ProcessedTasks WHERE RecordID='" + expenseID + "';";
                ExpenseRepository.ExeSqlCommon(sql);
                return new OperationResult(OperationResultType.Success, "删除成功", record);
            }
            catch (Exception ex)
            {
                return new OperationResult(OperationResultType.Error, "删除失败");
            }
        }
        #endregion

        #region 详细信息（支表一、二、三）--luozx
        //支表一-上每项的SQL语句定义
        private readonly string VMExpenseFirstTopSql = @"select ft.ID as ID,ft.BDID as SectionID,ft.QCID as PeriodID,'Name' as Name,ft.hj as Total,
ft.bz100 as OneOOZ,ft.bz200 as TwoOOZ,ft.bz300 as ThreeOOZ,ft.bz400 as FourOOZ,ft.bz500 as FiveOOZ,ft.bz600 as SixOOZ,ft.bz700 as SevenOOZ,
ft.bzzlj as ProvisionalMoney,ft.zxzlj as ProProvisionalMoney from REP_ZQJKZF1 as ft where bdid='{0}' and qcid={1} and jllx={2}";

        //支表一-下每项的SQL语句定义
        private readonly string VMExpenseFirstBottomSql = @"select ft.ID as ID,ft.BDID as SectionID,ft.QCID as PeriodID,'LeftName' as LeftName,'RightName' as RightName,ft.hj as Total,
ft.bz100 as OneOOZ,ft.bz200 as TwoOOZ,ft.bz300 as ThreeOOZ,ft.bz400 as FourOOZ,ft.bz500 as FiveOOZ,ft.bz600 as SixOOZ,ft.bz700 as SevenOOZ,
ft.bzzlj as ProvisionalMoney,ft.zxzlj as ProProvisionalMoney from REP_ZQJKZF1 as ft where bdid='{0}' and qcid={1} and jllx={2}";

        //支表一-扣款SQL语句定义
        private readonly string VMExpenseFirstCutPaymentSql = @"select ft.ID as ID,ft.BDID as SectionID,ft.QCID as PeriodID,'LeftName' as LeftName,'RightName' as RightName,
ft.BQKCDYYFK as CutPaymentForPerson,ft.KHJG as CutPaymentForMaterial,ft.KSJ as CutPaymentForTaxation,ft.KBLJ as CutPaymentForSave,
ft.KHGZ as CutPaymentForSalary,ft.WYFJ as CutPaymentForPenalty,ft.KKHJ as CutPaymentForTotal,ft.SJZFJE as CutPaymentForReality
from REP_ZQJKZF2 as ft where bdid='{0}' and qcid={1} and jllx={2}";

        //支表三SQL语句定义
        //        private readonly string VMExpenseThirdSql = @"select    a.ID as ID,
        //                                                                a.BDID as SectionID,
        //                                                                a.QCID as PeriodID,
        //                                                                a.QDBH as ListNo,
        //                                                                a.QDMC as ListName,
        //                                                                a.DW as Unit,
        //                                                                a.HTDJ as Price,
        //                                                                a.HTSL as ContractCount,
        //                                                                b.HTJE as ContractMoney,
        //                                                                case when b.SGTYX=1 then b.SGTSL else 0 end as BuildPaperCount,
        //                                                                case when b.SGTYX=1 then b.SGTJE else 0 end as BuildPaperMoney,
        //                                                                case when b.SGTYX=1 then b.SGTSL else a.HTSL end as ChangeCount,
        //                                                                case when b.SGTYX=1 then b.SGTJE else b.HTJE end as ChangeMoney,
        //                                                                a.BQJL as ThisPeriodFinishCount,
        //                                                                a.BQJLJE as ThisPeriodFinishMoney,
        //                                                                a.SQMJL as PreviousPeriodTotalFinishCount,
        //                                                                a.SQMJLJE as PreviousPeriodTotalFinishMoney,
        //                                                                a.BQMJL as ThisPeriodTotalFinishCount,
        //                                                                a.BQMJLJE as ThisPeriodTotalFinishMoney 
        //                                                      from REP_ZQZFZS a 
        //                                                      left join JLQD b on a.qdbh=b.qdbh 
        //                                                      where a.bdid='{0}' and b.bdid='{0}' and a.qcid={1} and lx=0 and (yjd=1 or yjd=-2) order by ListNo";
        public readonly string VMExpenseThirdSql = @" select a.ID as ID,a.BDID as SectionID,'{1}' as PeriodID,a.QDBH as ListNo,a.QDMC as ListName,a.DW as Unit, a.zbdj as Price,a.htsl as ContractCount,a.htje as ContractMoney,
                                        case when a.sgtyx=1 then a.sgtsl else 0 end as BuildPaperCount,
                                        case when a.sgtyx=1 then a.sgtje else 0 end as BuildPaperMoney,
                                        case when a.sgtyx=1 then a.sgtsl else a.htsl end as ChangeCount,
                                        case when a.sgtyx=1 then a.sgtje else a.htje end as ChangeMoney,
                                        b.BQJL as ThisPeriodFinishCount,
                                        b.BQJLJE as ThisPeriodFinishMoney,
                                        b.SQMJL as PreviousPeriodTotalFinishCount,
                                        b.SQMJLJE as PreviousPeriodTotalFinishMoney,
                                        b.BQMJL as ThisPeriodTotalFinishCount,
                                        b.BQMJLJE as ThisPeriodTotalFinishMoney 
                                        from (select * from JLQD where bdid='{0}') as a 
                                        left join 
                                        (select * from REP_ZQZFZS  where bdid='{0}' and QCID={1} and  lx=0 and (yjd=1 or yjd=-2) ) as b
                                        on a.QDBH=b.QDBH
                                        where a.pid!='-1' and ((a.HTJE is not null and a.HTJE<>0) or (a.sgtje is not null and a.sgtje<>0))
                                        order by a.QDBH";

        /// <summary>
        /// 支表一-上（列表每项）
        /// </summary>
        /// <param name="sectionID">标段ID</param>
        /// <param name="periodID">期次ID</param>
        /// <param name="flag">筛选标记</param>
        /// <param name="name">每项名称</param>
        /// <returns></returns>
        public List<VMExpenseFirstTopList> GetVMExpenseFirstTopEveryList(string sectionID, int periodID, int flag, string name)
        {
            List<VMExpenseFirstTopList> result = new List<VMExpenseFirstTopList>();
            string sql = string.Format(VMExpenseFirstTopSql, sectionID, periodID, flag);
            result = DbSqlHelper<VMExpenseFirstTopList>.SqlQueryCommon(sql);
            if (result != null && result.Count > 0)
            {
                //给每行指定名称
                result.ForEach(m => m.Name = name);
            }
            //如果取出空数据，仍需要加入数据行，用于显示名称等信息
            else
            {
                result.Add(new VMExpenseFirstTopList() { Name = name });
            }
            return result;
        }

        /// <summary>
        /// 支表一-下（列表每项）
        /// </summary>
        /// <param name="sectionID">标段ID</param>
        /// <param name="periodID">期次ID</param>
        /// <param name="flag">筛选标记</param>
        /// <param name="leftName">每项左名称</param>
        /// <param name="rightName">每项右名称</param>
        /// <returns></returns>
        public List<VMExpenseFirstBottomList> GetVMExpenseFirstBottomEveryList(string sectionID, int periodID, int flag, string leftName, string rightName)
        {
            List<VMExpenseFirstBottomList> result = new List<VMExpenseFirstBottomList>();
            string sql = string.Format(VMExpenseFirstBottomSql, sectionID, periodID, flag);
            result = DbSqlHelper<VMExpenseFirstBottomList>.SqlQueryCommon(sql);
            if (result != null && result.Count > 0)
            {
                //给每行指定左右名称
                result.ForEach(m => { m.LeftName = leftName; m.RightName = rightName; });
            }
            //如果取出空数据，仍需要加入数据行，用于显示名称等信息
            else
            {
                result.Add(new VMExpenseFirstBottomList() { LeftName = leftName, RightName = rightName });
            }
            return result;
        }

        /// <summary>
        /// 支表一-扣款（列表每项）
        /// </summary>
        /// <param name="sectionID">标段ID</param>
        /// <param name="periodID">期次ID</param>
        /// <param name="flag">筛选标记</param>
        /// <param name="leftName">每项左名称</param>
        /// <param name="rightName">每项右名称</param>
        /// <returns></returns>
        public List<VMExpenseFirstCutPayment> GetVMExpenseFirstCutPaymentEveryList(string sectionID, int periodID, int flag, string leftName, string rightName)
        {
            List<VMExpenseFirstCutPayment> result = new List<VMExpenseFirstCutPayment>();
            string sql = string.Format(VMExpenseFirstCutPaymentSql, sectionID, periodID, flag);
            result = DbSqlHelper<VMExpenseFirstCutPayment>.SqlQueryCommon(sql);
            if (result != null && result.Count > 0)
            {
                //给每行指定左右名称
                result.ForEach(m => { m.LeftName = leftName; m.RightName = rightName; });
            }
            //如果取出空数据，仍需要加入数据行，用于显示名称等信息
            else
            {
                result.Add(new VMExpenseFirstCutPayment { LeftName = leftName, RightName = rightName });
            }
            return result;
        }

        /// <summary>
        /// 支表一-上（列表）
        /// </summary>
        /// <param name="sectionID"></param>
        /// <param name="periodID"></param>
        /// <returns></returns>
        public List<VMExpenseFirstTopList> GetVMExpenseFirstTopList(string sectionID, int periodID)
        {
            List<VMExpenseFirstTopList> result = new List<VMExpenseFirstTopList>();

            List<VMExpenseFirstTopList> everyList = null;
            //合同总金额0
            everyList = this.GetVMExpenseFirstTopEveryList(sectionID, periodID, 0, "合同总金额");
            result.AddRange(everyList);
            //施工图纸金额1
            everyList = this.GetVMExpenseFirstTopEveryList(sectionID, periodID, 1, "施工图纸金额");
            result.AddRange(everyList);
            //批复变更增减金额2
            everyList = this.GetVMExpenseFirstTopEveryList(sectionID, periodID, 2, "批复变更增减金额");
            result.AddRange(everyList);
            //变更后总金额3
            everyList = this.GetVMExpenseFirstTopEveryList(sectionID, periodID, 3, "变更后总金额");
            result.AddRange(everyList);

            return result;
        }

        /// <summary>
        /// 支表一-下（列表）
        /// </summary>
        /// <param name="sectionID"></param>
        /// <param name="periodID"></param>
        /// <returns></returns>
        public List<VMExpenseFirstBottomList> GetVMExpenseFirstBottomList(string sectionID, int periodID)
        {
            List<VMExpenseFirstBottomList> result = new List<VMExpenseFirstBottomList>();
            List<VMExpenseFirstBottomList> everyList = null;
            //本期末累计支付 合计8
            everyList = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID, 8, "本期末累计支付", "合计");
            result.AddRange(everyList);
            //本期末累计支付 实际清单计量金额6
            everyList = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID, 6, "本期末累计支付", "实际清单计量金额");
            result.AddRange(everyList);
            //本期末累计支付 实际变更计量金额7
            everyList = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID, 7, "本期末累计支付", "实际变更计量金额");
            result.AddRange(everyList);

            //上期末累计支付 合计8
            everyList = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID - 1, 8, "上期末累计支付", "合计");
            result.AddRange(everyList);
            //上期末累计支付 实际清单计量金额6
            everyList = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID - 1, 6, "上期末累计支付", "实际清单计量金额");
            result.AddRange(everyList);
            //上期末累计支付 实际变更计量金额7
            everyList = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID - 1, 7, "上期末累计支付", "实际变更计量金额");
            result.AddRange(everyList);

            //本期支付 合计9
            everyList = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID, 9, "本期支付", "合计");
            result.AddRange(everyList);
            //本期支付 实际清单计量金额4
            everyList = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID, 4, "本期支付", "实际清单计量金额");
            result.AddRange(everyList);
            //本期支付 实际变更计量金额5
            everyList = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID, 5, "本期支付", "实际变更计量金额");
            result.AddRange(everyList);

            return result;
        }

        /// <summary>
        /// 支表一应扣款项
        /// </summary>
        /// <param name="sectionID"></param>
        /// <param name="periodID"></param>
        /// <returns></returns>
        public List<VMExpenseFirstCutPayment> GetVMExpenseFirstCutPaymentList(string sectionID, int periodID)
        {
            List<VMExpenseFirstCutPayment> result = new List<VMExpenseFirstCutPayment>();

            List<VMExpenseFirstCutPayment> everyList = null;
            //应扣款项 本期末累计1
            everyList = this.GetVMExpenseFirstCutPaymentEveryList(sectionID, periodID, 1, "应扣款项", "本期末累计");
            result.AddRange(everyList);
            //应扣款项 上期末累计1
            everyList = this.GetVMExpenseFirstCutPaymentEveryList(sectionID, periodID - 1, 1, "应扣款项", "上期末累计");
            result.AddRange(everyList);
            //应扣款项 本期0
            everyList = this.GetVMExpenseFirstCutPaymentEveryList(sectionID, periodID, 0, "应扣款项", "本期");
            result.AddRange(everyList);

            return result;
        }

        public List<VMExpenseSecond> GetVMExpenseSecondList(string sectionID, int periodID)
        {
            List<VMExpenseSecond> lstVMExpenseSecond = new List<VMExpenseSecond>();

            ConstructSecondList(sectionID, periodID, 0, "合同金额", "单项占合同总金额比（%）", "", "", 1, lstVMExpenseSecond);
            ConstructSecondList(sectionID, periodID, 1, "复核图纸金额", "单项占复核图纸总金额比（%）", "", "", 3, lstVMExpenseSecond);
            ConstructSecondList(sectionID, periodID, 3, "变更后金额", "单项占变更后总金额比（%）", "本期末完成金额", "单项完成占变更后金额比（%）", 5, lstVMExpenseSecond);
            return lstVMExpenseSecond;
        }

        private void ConstructSecondList(string sectionID, int periodID, int searchNum, string name, string name1, string name2, string name3, int orderNo, List<VMExpenseSecond> lstVMExpenseSecond)
        {
            VMExpenseFirstTopList data = this.GetVMExpenseFirstTopEveryList(sectionID, periodID, searchNum, name).FirstOrDefault();
            VMExpenseSecond numData = new VMExpenseSecond();
            numData.Name = data.Name;
            numData.OneOOZ = data.OneOOZ == null ? "0" : Math.Round(Convert.ToDecimal(data.OneOOZ), 0).ToString();
            numData.TwoOOZ = data.TwoOOZ == null ? "0" : Math.Round(Convert.ToDecimal(data.TwoOOZ), 0).ToString();
            numData.ThreeOOZ = data.ThreeOOZ == null ? "0" : Math.Round(Convert.ToDecimal(data.ThreeOOZ), 0).ToString();
            numData.FourOOZ = data.FourOOZ == null ? "0" : Math.Round(Convert.ToDecimal(data.FourOOZ), 0).ToString();
            numData.FiveOOZ = data.FiveOOZ == null ? "0" : Math.Round(Convert.ToDecimal(data.FiveOOZ), 0).ToString();
            numData.SixOOZ = data.SixOOZ == null ? "0" : Math.Round(Convert.ToDecimal(data.SixOOZ), 0).ToString();
            numData.SevenOOZ = data.SevenOOZ == null ? "0" : Math.Round(Convert.ToDecimal(data.SevenOOZ), 0).ToString();
            numData.ProvisionalMoney = data.ProvisionalMoney == null ? "0" : Math.Round(Convert.ToDecimal(data.ProvisionalMoney), 0).ToString();
            numData.ProProvisionalMoney = data.ProProvisionalMoney == null ? "0" : Math.Round(Convert.ToDecimal(data.ProProvisionalMoney), 0).ToString();
            numData.OrderNo = orderNo;
            lstVMExpenseSecond.Add(numData);

            VMExpenseSecond proportionData = new VMExpenseSecond();
            proportionData.Name = name1;
            if (data.Total != null && data.Total != 0)
            {
                proportionData.OneOOZ = data.OneOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(data.OneOOZ / data.Total * 100), 2) + "%";
                proportionData.TwoOOZ = data.TwoOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(data.TwoOOZ / data.Total * 100), 2) + "%";
                proportionData.ThreeOOZ = data.ThreeOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(data.ThreeOOZ / data.Total * 100), 2) + "%";
                proportionData.FourOOZ = data.FourOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(data.FourOOZ / data.Total * 100), 2) + "%";
                proportionData.FiveOOZ = data.FiveOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(data.FiveOOZ / data.Total * 100), 2) + "%";
                proportionData.SixOOZ = data.SixOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(data.SixOOZ / data.Total * 100), 2) + "%";
                proportionData.SevenOOZ = data.SevenOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(data.SevenOOZ / data.Total * 100), 2) + "%";
                proportionData.ProvisionalMoney = data.ProvisionalMoney == null ? "0%" : Math.Round(Convert.ToDecimal(data.ProvisionalMoney / data.Total * 100), 2) + "%";
                proportionData.ProProvisionalMoney = data.ProProvisionalMoney == null ? "0%" : Math.Round(Convert.ToDecimal(data.ProProvisionalMoney / data.Total * 100), 2) + "%";
            }
            else
            {
                proportionData.OneOOZ = "0%";
                proportionData.TwoOOZ = "0%";
                proportionData.ThreeOOZ = "0%";
                proportionData.FourOOZ = "0%";
                proportionData.FiveOOZ = "0%";
                proportionData.SixOOZ = "0%";
                proportionData.SevenOOZ = "0%";
                proportionData.ProvisionalMoney = "0%";
                proportionData.ProProvisionalMoney = "0%";
            }
            proportionData.OrderNo = orderNo + 1;
            lstVMExpenseSecond.Add(proportionData);

            if (searchNum == 3)
            {
                VMExpenseFirstBottomList vmExpenseFirstBottom = this.GetVMExpenseFirstBottomEveryList(sectionID, periodID, 8, "本期末累计支付", "合计").FirstOrDefault();
                VMExpenseSecond numTwoData = new VMExpenseSecond();
                numTwoData.Name = name2;
                numTwoData.OneOOZ = vmExpenseFirstBottom.OneOOZ == null ? "0" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.OneOOZ), 0).ToString();
                numTwoData.TwoOOZ = vmExpenseFirstBottom.TwoOOZ == null ? "0" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.TwoOOZ), 0).ToString();
                numTwoData.ThreeOOZ = vmExpenseFirstBottom.ThreeOOZ == null ? "0" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.ThreeOOZ), 0).ToString();
                numTwoData.FourOOZ = vmExpenseFirstBottom.FourOOZ == null ? "0" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.FourOOZ), 0).ToString();
                numTwoData.FiveOOZ = vmExpenseFirstBottom.FiveOOZ == null ? "0" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.FiveOOZ), 0).ToString();
                numTwoData.SixOOZ = vmExpenseFirstBottom.SixOOZ == null ? "0" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.SixOOZ), 0).ToString();
                numTwoData.SevenOOZ = vmExpenseFirstBottom.SevenOOZ == null ? "0" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.SevenOOZ), 0).ToString();
                numTwoData.ProvisionalMoney = vmExpenseFirstBottom.ProvisionalMoney == null ? "0" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.ProvisionalMoney), 0).ToString();
                numTwoData.ProProvisionalMoney = vmExpenseFirstBottom.ProProvisionalMoney == null ? "0" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.ProProvisionalMoney), 0).ToString();
                numTwoData.OrderNo = orderNo + 2;
                lstVMExpenseSecond.Add(numTwoData);

                VMExpenseSecond proportionTwoData = new VMExpenseSecond();
                proportionTwoData.Name = name3;
                if (data.OneOOZ != null && data.OneOOZ != 0)
                {
                    proportionTwoData.OneOOZ = vmExpenseFirstBottom.OneOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.OneOOZ / data.OneOOZ * 100), 2) + "%";
                }
                else
                {
                    proportionTwoData.OneOOZ = "0%";
                }
                if (data.TwoOOZ != null && data.TwoOOZ != 0)
                {
                    proportionTwoData.TwoOOZ = vmExpenseFirstBottom.TwoOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.TwoOOZ / data.TwoOOZ * 100), 2) + "%";
                }
                else
                {
                    proportionTwoData.TwoOOZ = "0%";
                }
                if (data.ThreeOOZ != null && data.ThreeOOZ != 0)
                {
                    proportionTwoData.ThreeOOZ = vmExpenseFirstBottom.ThreeOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.ThreeOOZ / data.ThreeOOZ * 100), 2) + "%";
                }
                else
                {
                    proportionTwoData.ThreeOOZ = "0%";
                }
                if (data.FourOOZ != null && data.FourOOZ != 0)
                {
                    proportionTwoData.FourOOZ = vmExpenseFirstBottom.FourOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.FourOOZ / data.FourOOZ * 100), 2) + "%";
                }
                else
                {
                    proportionTwoData.FourOOZ = "0%";
                }
                if (data.FiveOOZ != null && data.FiveOOZ != 0)
                {
                    proportionTwoData.FiveOOZ = vmExpenseFirstBottom.FiveOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.FiveOOZ / data.FiveOOZ * 100), 2) + "%";
                }
                else
                {
                    proportionTwoData.FiveOOZ = "0%";
                }
                if (data.SixOOZ != null && data.SixOOZ != 0)
                {
                    proportionTwoData.SixOOZ = vmExpenseFirstBottom.SixOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.SixOOZ / data.SixOOZ * 100), 2) + "%";
                }
                else
                {
                    proportionTwoData.SixOOZ = "0%";
                }
                if (data.SevenOOZ != null && data.SevenOOZ != 0)
                {
                    proportionTwoData.SevenOOZ = vmExpenseFirstBottom.SevenOOZ == null ? "0%" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.SevenOOZ / data.SevenOOZ * 100), 2) + "%";
                }
                else
                {
                    proportionTwoData.SevenOOZ = "0%";
                }
                if (data.ProvisionalMoney != null && data.ProvisionalMoney != 0)
                {
                    proportionTwoData.ProvisionalMoney = vmExpenseFirstBottom.ProvisionalMoney == null ? "0%" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.ProvisionalMoney / data.ProvisionalMoney * 100), 2) + "%";
                }
                else
                {
                    proportionTwoData.ProvisionalMoney = "0%";
                }
                if (data.ProProvisionalMoney != null && data.ProProvisionalMoney != 0)
                {
                    proportionTwoData.ProProvisionalMoney = vmExpenseFirstBottom.ProProvisionalMoney == null ? "0%" : Math.Round(Convert.ToDecimal(vmExpenseFirstBottom.ProProvisionalMoney / data.ProProvisionalMoney * 100), 2) + "%";
                }
                else
                {
                    proportionTwoData.ProProvisionalMoney = "0%";
                }
                proportionTwoData.OrderNo = orderNo + 3;
                lstVMExpenseSecond.Add(proportionTwoData);
            }
        }

        /// <summary>
        /// 支表三列表
        /// </summary>
        /// <param name="sectionID"></param>
        /// <param name="periodID"></param>
        /// <returns></returns>
        public List<VMExpenseThirdList> GetVMExpenseThirdList(string sectionID, int periodID)
        {
            List<VMExpenseThirdList> result = new List<VMExpenseThirdList>();
            //支表三对应的SQL语句
            string sql = string.Format(VMExpenseThirdSql, sectionID, periodID);
            result = DbSqlHelper<VMExpenseThirdList>.SqlQueryCommon(sql);
            return result;
        }
        #endregion

        #region 计量数据--luozx
        //期次数据
        private readonly string PeriodSql = @"select qc.QCID as PeriodID,0.0 as ApproveMoney,'未申请' as ApplyStatus from XMQC as qc 
where qc.BDID='{0}'
order by qc.QCID";
        //期次对应的批复金额
        private readonly string VMEstimateDataSql = @"select zf.QCID as PeriodID,zf.SJZFJE AS ApproveMoney,'' as ApplyStatus from REP_ZQJKZF2 as zf
where zf.BDID='{0}' and zf.QCID='{1}' and ZF.JLLX='0'";
        //是否已经申请过
        private readonly string ExistEstimateDataSql = @"select COUNT(1) as Count from ExpenseBackups where ContractID='{0}' and ContractStages='{1}'";

        public List<VMEstimateData> GetVMEstimateDataList(string sectionID)
        {
            List<VMEstimateData> result = new List<VMEstimateData>();

            //先取得期次的信息
            List<VMEstimateData> periodDataList = this.GetPeriodDataList(sectionID);
            //如果有期次信息，则往期次信息的批复金额赋值
            if (periodDataList != null && periodDataList.Count > 0)
            {
                foreach (var periodData in periodDataList)
                {
                    VMEstimateData approveData = this.GetApproveData(sectionID, periodData.PeriodID);
                    if (approveData != null)
                    {
                        periodData.ApproveMoney = approveData.ApproveMoney;
                    }
                    bool isApplied = this.IsApplied(sectionID, periodData.PeriodID);
                    if (isApplied)
                    {
                        periodData.ApplyStatus = "已申请";
                    }
                    result.Add(periodData);
                }
            }
            return result;
        }

        /// <summary>
        /// 获取期次的列表信息（批复金额此时为0）
        /// </summary>
        /// <param name="sectionID"></param>
        /// <returns></returns>
        private List<VMEstimateData> GetPeriodDataList(string sectionID)
        {
            List<VMEstimateData> result = new List<VMEstimateData>();
            string sql = string.Format(PeriodSql, sectionID);
            result = DbSqlHelper<VMEstimateData>.SqlQueryCommon(sql);
            return result;
        }

        /// <summary>
        /// 找出对应标段对应期次的批复金额（即本期实际支付金额）
        /// </summary>
        /// <param name="sectionID"></param>
        /// <param name="periodID"></param>
        /// <returns></returns>
        private VMEstimateData GetApproveData(string sectionID, string periodID)
        {
            VMEstimateData result = null;
            string sql = string.Format(VMEstimateDataSql, sectionID, periodID);
            var approveDataList = DbSqlHelper<VMEstimateData>.SqlQueryCommon(sql);
            if (approveDataList != null && approveDataList.Count > 0)
            {
                result = new VMEstimateData();
                result = approveDataList[0];
            }
            return result;
        }

        /// <summary>
        /// 判断是否已经申请过
        /// </summary>
        /// <param name="section"></param>
        /// <param name="periodID"></param>
        /// <returns></returns>
        private bool IsApplied(string sectionID, string periodID)
        {
            bool result = false;
            string sql = string.Format(ExistEstimateDataSql, sectionID, periodID);
            var sqlResult = DbSqlHelper<CountEntity>.SqlQueryCommon(sql);
            if (sqlResult != null && sqlResult.Count > 0)
            {
                var countEntity = sqlResult[0];
                if (countEntity.Count > 0)
                    result = true;
            }
            return result;
        }

        public UpdateConstructionContractEstimateView GetUpdateConstructionContractEstimate(string contractID, string periodID)
        {
            UpdateConstructionContractEstimateView updateConstructionContractEstimateView = new UpdateConstructionContractEstimateView();
            updateConstructionContractEstimateView.ApproveMoney = 0;
            updateConstructionContractEstimateView.Message = "更新成功！";
            VMEstimateData VMEstimateData = GetApproveData(contractID, periodID);
            if (VMEstimateData != null && VMEstimateData.ApproveMoney != null)
            {
                updateConstructionContractEstimateView.ApproveMoney = Convert.ToDecimal(VMEstimateData.ApproveMoney);
            }
            return updateConstructionContractEstimateView;
        }
        #endregion


        #region 支表二 图表部分的支付数据
        public List<VMQCYearMonth> GetVMQCYearMonth(string sectionID, int periodID)
        {
            string VMQCYearMonthSql = @"SELECT Convert(varchar,DATEPART(year, JSRQ)) as Year, Convert(varchar,DATEPART(month, JSRQ)) as Month FROM XMQC WHERE BDID='" + sectionID + "' and QCID='" + periodID + "'";
            List<VMQCYearMonth> lstVMQCYearMonth = DbSqlHelper<VMQCYearMonth>.SqlQueryCommon(VMQCYearMonthSql);
            return lstVMQCYearMonth;
        }

        public List<PointView> GetZFComplete(string sectionID, int periodID)
        {
            string VMREP_ZQJKZF1Sql = @"
SELECT YM.Year,YM.Month,Proportion.Proportion FROM 
(SELECT QCID, DATEPART(year, JSRQ) as Year, DATEPART(month, JSRQ) as Month FROM XMQC
WHERE BDID='" + sectionID + @"' and QCID<=" + periodID + @" and JSRQ<CONVERT(datetime,'2017-1-1')) AS YM
INNER JOIN 
(select QCID ,
(SELECT HJ FROM REP_ZQJKZF1 where BDID='" + sectionID + @"' AND a.QCID=QCID and  JLLX=8)/(SELECT HJ FROM REP_ZQJKZF1 where BDID='" + sectionID + @"' AND a.QCID=QCID and  JLLX=3)*100 as Proportion 
FROM REP_ZQJKZF1 as a
WHERE BDID='" + sectionID + @"' and QCID<=" + periodID + @" 
GROUP BY QCID) AS Proportion
ON YM.QCID=Proportion.QCID
ORDER BY YM.QCID";
            List<VMREP_ZQJKZF1> lstVMREP_ZQJKZF1 = DbSqlHelper<VMREP_ZQJKZF1>.SqlQueryCommon(VMREP_ZQJKZF1Sql);
            List<VMREP_ZQJKZF1> lstVMREP_ZQJKZF1Large2013 = lstVMREP_ZQJKZF1.Where(v => v.Year > 2013).ToList();

            List<PointView> lstPointView = new List<PointView>();
            if (lstVMREP_ZQJKZF1Large2013.Count > 0)
            {
                if (lstVMREP_ZQJKZF1Large2013.Count == lstVMREP_ZQJKZF1.Count)
                {
                    int x = GetX(lstVMREP_ZQJKZF1[0].Year, lstVMREP_ZQJKZF1[0].Month);
                    int xNew = 0;
                    if (x > 0)
                    {
                        xNew = x - 1;
                    }
                    lstPointView.Add(new PointView() { X = xNew, Y = 0 });
                }
                else
                {
                    List<VMREP_ZQJKZF1> lstVMREP_ZQJKZF1Litter2014 = lstVMREP_ZQJKZF1.Where(v => v.Year < 2014).OrderBy(v => v.Year).ThenBy(v => v.Month).ToList();
                    lstPointView.Add(new PointView() { X = 0, Y = Convert.ToDouble(lstVMREP_ZQJKZF1[lstVMREP_ZQJKZF1.Count - 1].Proportion) });
                }
                for (int i = 0; i < lstVMREP_ZQJKZF1.Count; i++)
                {
                    int x = GetX(lstVMREP_ZQJKZF1[i].Year, lstVMREP_ZQJKZF1[i].Month);
                    lstPointView.Add(new PointView() { X = x, Y = Convert.ToDouble(lstVMREP_ZQJKZF1[i].Proportion) });
                }
            }

            lstPointView = lstPointView.OrderBy(p => p.X).ToList();
            List<PointView> lstReturnPointView = new List<PointView>();
            for (int i = 0; i < lstPointView.Count; i++)
            {
                lstReturnPointView.Add(new PointView() { X = lstPointView[i].X, Y = lstPointView[i].Y });
                if (i + 1 < lstPointView.Count && lstPointView[i + 1].X > lstPointView[i].X + 1)
                {
                    for (int j = lstPointView[i].X + 1; j < lstPointView[i + 1].X; j++)
                    {
                        lstReturnPointView.Add(new PointView() { X = j, Y = lstPointView[i].Y });
                    }
                }
            }
            return lstReturnPointView;
        }

        private int GetX(int year, int month)
        {
            int yearPart = year - 2014;
            return yearPart * 12 + month;
        }
        #endregion

        #region 进度查询
        public List<PointView> GetZFCompleteForMulContract(string projectID, List<string> lstContractID, string contractIDSQLWhere)
        {
            //获取合同总额
            string sqlTotalHJ = @"  SELECT Convert(decimal,SUM(A.HJ)) as HJ FROM(
                                    SELECT BDID,Sum(case when sgtyx=1 then sgtje else htje end) as hj from jlqd WHERE cjm<>0 GROUP BY BDID
                                    UNION ALL
                                    SELECT BDID,SUM(bgzjje) as hj from BGLQD GROUP BY BDID) AS A 
                                    WHERE A.BDID IN (" + contractIDSQLWhere + @")";
            List<TotalHJ> lstTotalHJ = DbSqlHelper<TotalHJ>.SqlQueryCommon(sqlTotalHJ);
            List<PointView> lstPointView = new List<PointView>();

            if (lstTotalHJ.Count == 1 && lstTotalHJ[0].HJ != null)
            {
                //获取计量数据
                string sqlVMREP_ZQJKZF1ForScheduleSearch = @"SELECT QC.BDID,QC.QCID, DATEPART(year, JSRQ) as Year, DATEPART(month, JSRQ) as Month,JL.JLLX,JL.hj AS HJ
                                                FROM XMQC AS QC
                                                INNER JOIN REP_ZQJKZF1 AS JL
                                                ON QC.BDID=JL.BDID AND QC.QCID=JL.QCID
                                                WHERE XMID ='" + projectID + "' AND QC.BDID IN (" + contractIDSQLWhere + @") AND JSRQ<CONVERT(datetime,'2017-1-1') AND JL.JLLX IN(8)";
                List<VMREP_ZQJKZF1ForScheduleSearch> lstVMREP_ZQJKZF1ForScheduleSearch = DbSqlHelper<VMREP_ZQJKZF1ForScheduleSearch>.SqlQueryCommon(sqlVMREP_ZQJKZF1ForScheduleSearch);

                //获取小于2014的最近的一个年月
                var lstVMQCYearMonthFirstLittle2014 = (from yp in lstVMREP_ZQJKZF1ForScheduleSearch
                                                       group yp by new { Year = yp.Year, Month = yp.Month } into temp
                                                       orderby temp.Key.Year, temp.Key.Month descending
                                                       where temp.Key.Year < 2014
                                                       select new { temp.Key.Year, temp.Key.Month }).ToList().FirstOrDefault();
                //构造第一条数据
                int count = 1;
                if (lstVMQCYearMonthFirstLittle2014 != null)
                {
                    count++;
                    decimal totalForAlter = 0;
                    foreach (string contractID in lstContractID)
                    {
                        VMREP_ZQJKZF1ForScheduleSearch REP_ZQJKZF1 = lstVMREP_ZQJKZF1ForScheduleSearch.Where(z => z.BDID == contractID && ((z.Year == lstVMQCYearMonthFirstLittle2014.Year && z.Month <= lstVMQCYearMonthFirstLittle2014.Month) || (z.Year < lstVMQCYearMonthFirstLittle2014.Year))).OrderByDescending(z => z.Year).ThenByDescending(z => z.Month).FirstOrDefault();
                        if (REP_ZQJKZF1 != null)
                        {
                            totalForAlter += REP_ZQJKZF1.HJ == null ? 0 : Convert.ToDecimal(REP_ZQJKZF1.HJ);
                        }
                    }
                    double proportion = lstTotalHJ[0].HJ == 0 ? 0 : Math.Round(Convert.ToDouble(totalForAlter / lstTotalHJ[0].HJ * 100), 2);
                    lstPointView.Add(new PointView() { X = 0, Y = proportion });
                }

                //获取大于2013的年月
                var lstVMQCYearMonthLarge2013 = from yp in lstVMREP_ZQJKZF1ForScheduleSearch
                                                group yp by new { Year = yp.Year, Month = yp.Month } into temp
                                                orderby temp.Key.Year, temp.Key.Month ascending
                                                where temp.Key.Year >= 2014
                                                select new { temp.Key.Year, temp.Key.Month };
                foreach (var key in lstVMQCYearMonthLarge2013)
                {
                    decimal totalForAlter = 0;
                    foreach (string contractID in lstContractID)
                    {
                        VMREP_ZQJKZF1ForScheduleSearch REP_ZQJKZF1 = lstVMREP_ZQJKZF1ForScheduleSearch.Where(z => z.BDID == contractID && ((z.Year == key.Year && z.Month <= key.Month) || (z.Year < key.Year))).OrderByDescending(z => z.Year).ThenByDescending(z => z.Month).FirstOrDefault();
                        if (REP_ZQJKZF1 != null)
                        {
                            totalForAlter += REP_ZQJKZF1.HJ == null ? 0 : Convert.ToDecimal(REP_ZQJKZF1.HJ);
                        }
                    }

                    double proportion = lstTotalHJ[0].HJ == 0 ? 0 : Math.Round(Convert.ToDouble(totalForAlter / lstTotalHJ[0].HJ * 100), 2);
                    int x = GetX(key.Year, key.Month);
                    if (count == 1)
                    {
                        lstPointView.Add(new PointView() { X = x - 1, Y = 0 });
                    }
                    lstPointView.Add(new PointView() { X = x, Y = proportion });
                    count++;
                }
            }
            return lstPointView;
        }
        #endregion

        public List<ExpenseView> GetExpenseByProjectIDAndContractID(Guid projectID, Guid contractID)
        {
            var queryList = from e in ExpenseRepository.Entities
                            where e.ProjectID == projectID && e.ContractID == contractID && e.HandleState == "2"
                            select new
                            {
                                e.ID,
                                e.ContractStages
                            };

            List<ExpenseView> expenseViewList = new List<ExpenseView>();
            foreach (var query in queryList)
            {
                ExpenseView newExpenseView = new ExpenseView();
                newExpenseView.ID = query.ID;
                newExpenseView.ContractStages = query.ContractStages;
                newExpenseView.ContractStagesStr = "第" + query.ContractStages + "期";
                expenseViewList.Add(newExpenseView);
            }
            return expenseViewList.OrderBy(e => e.ContractStages).ToList();
        }

        public string GetExpensePayNum(Guid projectID, Guid contractID, int? ContractStages)
        {
            var queryList = from e in ExpenseRepository.Entities
                            where e.ProjectID == projectID && e.ContractID == contractID && e.ContractStages == ContractStages
                            select new
                            {
                                e.PayNum
                            };
            if (queryList != null && queryList.FirstOrDefault() != null && !string.IsNullOrEmpty(queryList.FirstOrDefault().PayNum))
            {
                return queryList.FirstOrDefault().PayNum;
            }
            else
            {
                return string.Empty;
            }
        }

        public void ReverseAudit(string expenseID, string flowNodeCode, string userName)
        {
            string sql = @"delete from ApprovalExpenses where ExpenseID='" + expenseID + @"';
                           delete from CompleteFlowNodes where RecordID='" + expenseID + "' and NodeCode='" + flowNodeCode + @"';
                           delete from FlowGraphNodes where RecordID='" + expenseID + "' and PathStartNodeCode='" + flowNodeCode + @"';
                           delete from FlowProcessMessages where RecordID='" + expenseID + "' and FlowNodeCode='" + flowNodeCode + "' and SponsorUser='" + userName + @"' and ProcessType='1';
                           delete from ProcessedTasks where RecordID='" + expenseID + "' and CurrentProcessNodeCode='" + flowNodeCode + "'";
            ExpenseRepository.ExeSqlCommon(sql);
        }
    }
}